// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System.Data.SqlClient;

namespace MsSqlSpatial
{
    /// <summary>
    /// </summary>
    public static class GeometryTable
    {
        internal static void ExecuteCreate(SqlCommand command, string schemaName, string tableName, string columnName,
                                           string createColumns)
        {
            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"CREATE TABLE [{0}].[{1}] (
oid bigint IDENTITY PRIMARY KEY
,[{2}] varbinary(max)
{3})"
                                                     , schemaName, tableName, columnName, createColumns));
            command.ExecuteNonQuery();
        }

        internal static void ExecuteDropTableDependantObjects(SqlCommand command, string schemaName, string tableName)
        {
            if (DatabaseHelper.CheckMetaTableExists(command, "GEOMETRY_COLUMNS"))
            {
                foreach (
                    string columnName in GeometryColumnsTable.GetGeometryColumnsByTable(command, schemaName, tableName))
                {
                    StoredProcedures.ExecuteDropGeometryQueryFunctions(command, schemaName, tableName, columnName);
                }
                GeometryColumnsTable.ExecuteDeleteMetaRecords(command, schemaName, tableName);
            }
        }

        internal static void ExecuteDropColumnDependantObjects(SqlCommand command, string schemaName, string tableName,
                                                               string columnName)
        {
            StoredProcedures.ExecuteDropGeometryQueryFunctions(command, schemaName, tableName, columnName);
            ExecuteDropTrigger(command, schemaName, tableName, columnName);
            ExecuteDropConstraintGeometryType(command, schemaName, tableName, columnName);
            ExecuteDropConstraintSRID(command, schemaName, tableName, columnName);
            ExecuteDropIndexes(command, schemaName, tableName, columnName);
            GeometryColumnsTable.ExecuteDeleteMetaRecord(command, schemaName, tableName, columnName);
        }

        internal static void ExecuteAddColumn(SqlCommand command, string schemaName, string tableName, string columnName)
        {
            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"ALTER TABLE [{0}].[{1}] 
ADD [{2}] varbinary(max)
"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();
        }

        public static void ExecuteAddConstraintGeometryType(SqlCommand command, string schemaName, string tableName,
                                                            string columnName, string type)
        {
            if (!string.IsNullOrEmpty(type) && type != "GEOMETRY")
            {
                int oldTimeout = command.CommandTimeout;
                command.CommandTimeout = 0;

                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"ALTER TABLE [{0}].[{1}]
ADD CONSTRAINT [enforce_type_{1}_{2}] 
CHECK ([ST].[GeometryType]([{2}]) = '{3}' OR [{2}] IS NULL)"
                                                         , schemaName, tableName, columnName, type));
                command.ExecuteNonQuery();

                command.CommandTimeout = oldTimeout;
            }
        }

        public static void ExecuteDropConstraintGeometryType(SqlCommand command, string schemaName, string tableName,
                                                             string columnName)
        {
            string constraintName = string.Format("enforce_type_{0}_{1}", tableName, columnName);
            if (DatabaseHelper.CheckConstraintExists(command, schemaName, tableName, constraintName))
            {
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"ALTER TABLE [{0}].[{1}]
DROP CONSTRAINT [{2}]"
                                                         , schemaName, tableName, constraintName));
                command.ExecuteNonQuery();
            }
        }

        public static void ExecuteAddConstraintSRID(SqlCommand command, string schemaName, string tableName,
                                                    string columnName, int srid)
        {
            int oldTimeout = command.CommandTimeout;
            command.CommandTimeout = 0;

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"ALTER TABLE [{0}].[{1}]
ADD CONSTRAINT [enforce_srid_{1}_{2}] 
CHECK ([{2}] IS NULL OR [ST].[SRID]([{2}]) = {3})"
                                                     , schemaName, tableName, columnName, srid));
            command.ExecuteNonQuery();
            command.CommandTimeout = oldTimeout;
        }

        public static void ExecuteDropConstraintSRID(SqlCommand command, string schemaName, string tableName,
                                                     string columnName)
        {
            string constraintName = string.Format("enforce_srid_{0}_{1}", tableName, columnName);
            if (DatabaseHelper.CheckConstraintExists(command, schemaName, tableName, constraintName))
            {
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"ALTER TABLE [{0}].[{1}]
DROP CONSTRAINT [{2}]"
                                                         , schemaName, tableName, constraintName));
                command.ExecuteNonQuery();
            }
        }

        public static void ExecuteAddIndexes(SqlCommand command, string schemaName, string tableName, string columnName)
        {
            int oldTimeout = command.CommandTimeout;
            command.CommandTimeout = 0;

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"ALTER TABLE [{0}].[{1}] 
ADD 
 [{2}_Envelope_MinX] float
,[{2}_Envelope_MinY] float
,[{2}_Envelope_MaxX] float
,[{2}_Envelope_MaxY] float
"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"UPDATE [{0}].[{1}] SET
 [{2}_Envelope_MinX] = ST.MinX([{2}])
,[{2}_Envelope_MinY] = ST.MinY([{2}])
,[{2}_Envelope_MaxX] = ST.MaxX([{2}])
,[{2}_Envelope_MaxY] = ST.MaxY([{2}])
"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"CREATE INDEX [IDX_{2}_Envelope_MinX] ON [{0}].[{1}] ([{2}_Envelope_MinX])"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"CREATE INDEX [IDX_{2}_Envelope_MinY] ON [{0}].[{1}] ([{2}_Envelope_MinY])"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"CREATE INDEX [IDX_{2}_Envelope_MaxX] ON [{0}].[{1}] ([{2}_Envelope_MaxX])"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"CREATE INDEX [IDX_{2}_Envelope_MaxY] ON [{0}].[{1}] ([{2}_Envelope_MaxY])"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"CREATE INDEX [IDX_{2}_Envelope] ON [{0}].[{1}] (
 [{2}_Envelope_MinX]
,[{2}_Envelope_MaxX]
,[{2}_Envelope_MinY]
,[{2}_Envelope_MaxY]
)"
                                                     , schemaName, tableName, columnName));
            command.ExecuteNonQuery();

            command.CommandTimeout = oldTimeout;
        }

        public static void ExecuteDropIndexes(SqlCommand command, string schemaName, string tableName, string columnName)
        {
            DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MinX");
            DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MinY");
            DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MaxX");
            DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MaxY");
            DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope");

            DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MinX");
            DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MinY");
            DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MaxX");
            DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MaxY");
        }

        private static string BuildTriggerName(string tableName, string columnName)
        {
            return "ST_GeometryTableTrigger_" + tableName + "_" + columnName;
        }

        public static void ExecuteCreateTrigger(SqlCommand command, string schemaName, string tableName,
                                                string columnName)
        {
            string triggerName = BuildTriggerName(tableName, columnName);

            if (!DatabaseHelper.CheckTriggerExists(command, schemaName, triggerName))
            {
                string primaryKey = GetPrimaryKeyColumn(command, schemaName, tableName);
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"CREATE TRIGGER [{0}].[{3}] 
ON [{0}].[{1}]
WITH EXECUTE AS CALLER 
AFTER UPDATE, INSERT 
AS
BEGIN
	IF UPDATE({2})
	BEGIN
		SET NOCOUNT ON

		UPDATE [{0}].[{1}] SET
		 [{2}_Envelope_MinX] = ST.MinX(inserted.[{2}])
		,[{2}_Envelope_MinY] = ST.MinY(inserted.[{2}])
		,[{2}_Envelope_MaxX] = ST.MaxX(inserted.[{2}])
		,[{2}_Envelope_MaxY] = ST.MaxY(inserted.[{2}])
		FROM inserted
		INNER JOIN [{0}].[{1}]
		ON inserted.[{4}] = [{0}].[{1}].[{4}]

		SET NOCOUNT OFF
	END
END"
                                                         , schemaName
                                                         , tableName
                                                         , columnName
                                                         , triggerName
                                                         , primaryKey
                                                         ));
                command.ExecuteNonQuery();
            }
        }

        public static void ExecuteDropTrigger(SqlCommand command, string schemaName, string tableName, string columnName)
        {
            string triggerName = BuildTriggerName(tableName, columnName);
            if (DatabaseHelper.CheckTriggerExists(command, schemaName, triggerName))
            {
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"DROP TRIGGER [{0}].[{1}]"
                                                         , schemaName, triggerName));
                command.ExecuteNonQuery();
            }
        }

        internal static string GetPrimaryKeyColumn(SqlCommand command, string schemaName, string tableName)
        {
            // Get primary key column name from table metadata
            DatabaseHelper.BuildCommand(command,
                                        @"EXECUTE sp_pkeys @p1, @p0"
                                        , schemaName, tableName);
            string primaryKey = null;
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    primaryKey = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
                }
            }

            // If doesn't have primary key, add it
            if (string.IsNullOrEmpty(primaryKey))
            {
                primaryKey = tableName + "_oid";
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"ALTER TABLE [{0}].[{1}]
ADD [{2}] bigint IDENTITY PRIMARY KEY"
                                                         , schemaName, tableName, primaryKey));
                command.ExecuteNonQuery();
            }

            return primaryKey;
        }
    }
}